# 根据客户编号customercode查询数据库中j_trade_order_hot表中的tradeid，然后通过tradeid进行关联，得到j_trade_order_goods_hot中的该客户的订单数据
import pymysql
from collections import defaultdict
import sys
import os
from typing import Dict, List

# 添加项目根目录到Python路径
project_root = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..'))
sys.path.insert(0, project_root)

# 数据字典：定义表名常量
TABLE_ORDER = 'j_trade_order_hot'
TABLE_ORDER_GOODS = 'j_trade_order_goods_hot'
TABLE_CUSTOMER = 'j_customer_hot'

# 字段映射配置
FIELD_MAPPING = {
    # 订单表字段
    'order': {
        'id': 'tradeid',  # 订单ID（用于关联）
        'number': 'tradeno',  # 订单号（显示用）
        'channel': 'shopname',  # 消费渠道
        'customer_code': 'customercode',  # 客户编号
        'amount': 'realfee',  # 实付金额
        'time': 'tradetime',  # 交易时间
    },
    # 订单商品表字段
    'order_goods': {
        'order_id': 'tradeid',  # 订单ID（外键）
        'name': 'goodsname',  # 商品名称
    },
    # 客户表字段
    'customer': {
        'code': 'customercode',  # 客户编号
        'phone': 'phone',  # 客户手机号
        'name': 'customername'  # 客户姓名
    }
}

# SQL查询模板
SQL_TEMPLATES = {
    'customer_orders': """
    SELECT 
        o.{order_number}, o.{order_channel}, o.{order_customer_code}, 
        o.{order_amount}, o.{order_time},
        GROUP_CONCAT(g.{goods_name} SEPARATOR '\n') as {goods_name}
    FROM 
        {table_order} o
    LEFT JOIN 
        {table_order_goods} g ON o.{order_id} = g.{goods_order_id}
    WHERE 
        o.{order_customer_code} = %s
    GROUP BY 
        g.{goods_order_id}, o.{order_channel}, o.{order_customer_code}, 
        o.{order_amount}, o.{order_time}
    ORDER BY 
        o.{order_time} DESC
    """,
    'customer_orders_by_phone': '''
    SELECT 
        o.tradeno, o.shopname, o.customercode, 
        o.realfee, o.tradetime, 
        GROUP_CONCAT(g.goodsname SEPARATOR '\n') as goodsname
    FROM 
        j_trade_order_hot o
    INNER JOIN 
        j_trade_order_goods_hot g ON o.tradeid = g.tradeid
    INNER JOIN 
        j_customer_hot c ON o.customercode = c.customercode       
    WHERE 
        c.phone = %s
    GROUP BY 
        g.tradeid, o.shopname, o.customercode, 
        o.realfee, o.tradetime
    ORDER BY 
        o.tradetime DESC
    '''
}

from config import DB_ZXY


def get_table_fields() -> Dict[str, List[str]]:
    """获取表字段信息"""
    fields_info = {}
    try:
        with DB_ZXY() as connection:
            with connection.cursor() as cursor:
                # 获取所有表名
                cursor.execute("SHOW TABLES")
                tables = cursor.fetchall()
                print("\n数据库中的表:")
                for table in tables:
                    print(table[0])

                # 获取订单表字段
                try:
                    cursor.execute(f"SHOW COLUMNS FROM {TABLE_ORDER}")
                    fields_info[TABLE_ORDER] = [field[0] for field in cursor.fetchall()]
                except pymysql.Error as e:
                    print(f"\n获取{TABLE_ORDER}表字段时出错: {e}")

                # 获取订单商品表字段
                try:
                    cursor.execute(f"SHOW COLUMNS FROM {TABLE_ORDER_GOODS}")
                    fields_info[TABLE_ORDER_GOODS] = [field[0] for field in cursor.fetchall()]
                except pymysql.Error as e:
                    print(f"\n获取{TABLE_ORDER_GOODS}表字段时出错: {e}")
    except pymysql.Error as e:
        print(f"\n连接数据库时出错: {e}")

    return fields_info


def get_customer_orders(customercode: str) -> List[Dict]:
    """根据客户编号查询订单信息"""
    from datetime import datetime

    with DB_ZXY() as connection:
        with connection.cursor(pymysql.cursors.DictCursor) as cursor:
            # 使用字段映射构建SQL查询
            query = SQL_TEMPLATES['customer_orders'].format(
                # 订单表字段
                order_number=FIELD_MAPPING['order']['number'],
                order_channel=FIELD_MAPPING['order']['channel'],
                order_customer_code=FIELD_MAPPING['order']['customer_code'],
                order_amount=FIELD_MAPPING['order']['amount'],
                order_time=FIELD_MAPPING['order']['time'],
                order_id=FIELD_MAPPING['order']['id'],
                # 订单商品表字段
                goods_name=FIELD_MAPPING['order_goods']['name'],
                goods_order_id=FIELD_MAPPING['order_goods']['order_id'],
                # 表名
                table_order=TABLE_ORDER,
                table_order_goods=TABLE_ORDER_GOODS
            )
            cursor.execute(query, (customercode,))
            results = cursor.fetchall()

            # 将tradetime转换为datetime对象
            for order in results:
                if order['tradetime']:
                    try:
                        if isinstance(order['tradetime'], str):
                            order['tradetime'] = datetime.strptime(order['tradetime'], '%Y-%m-%d %H:%M:%S')
                    except (ValueError, TypeError):
                        order['tradetime'] = None

            return results


def get_customer_orders_by_phone(phone: str) -> List[Dict]:
    """根据客户手机号查询订单信息"""
    from datetime import datetime

    with DB_ZXY() as connection:
        with connection.cursor(pymysql.cursors.DictCursor) as cursor:
            # 使用新的SQL模板查询
            query = SQL_TEMPLATES['customer_orders_by_phone']
            cursor.execute(query, (phone,))
            results = cursor.fetchall()

            # 将tradetime转换为datetime对象
            for order in results:
                if order['tradetime']:
                    try:
                        if isinstance(order['tradetime'], str):
                            order['tradetime'] = datetime.strptime(order['tradetime'], '%Y-%m-%d %H:%M:%S')
                    except (ValueError, TypeError):
                        order['tradetime'] = None

            return results


def calculate_performance_stats(orders: List[Dict]) -> Dict:
    """计算业绩统计数据"""
    # 获取字段映射中的字段名
    order_number = FIELD_MAPPING['order']['number']
    order_amount = FIELD_MAPPING['order']['amount']
    order_time = FIELD_MAPPING['order']['time']
    goods_name = FIELD_MAPPING['order_goods']['name']

    stats = {
        'total_orders': len(set(order[order_number] for order in orders)),  # 订单总数
        'total_amount': sum(float(order.get(order_amount, 0) or 0) for order in orders),  # 总金额
        'goods_stats': defaultdict(lambda: {'quantity': 1, 'amount': 0}),  # 按商品统计（由于没有数量字段，默认每个商品数量为1）
        'monthly_stats': defaultdict(lambda: {'orders': set(), 'amount': 0})  # 按月统计
    }

    for order in orders:
        # 商品统计
        goods = order.get(goods_name)
        if goods:
            amount = float(order.get(order_amount, 0) or 0)
            stats['goods_stats'][goods]['amount'] += amount

        # 月度统计
        time = order.get(order_time)
        if time:
            try:
                month = time.strftime('%Y-%m')
                stats['monthly_stats'][month]['orders'].add(order[order_number])
                stats['monthly_stats'][month]['amount'] += float(order.get(order_amount, 0) or 0)
            except (AttributeError, TypeError):
                # 如果time不是datetime对象或无法格式化，跳过这条记录
                continue

    # 转换月度统计中的订单集合为数量
    for month_stats in stats['monthly_stats'].values():
        month_stats['orders'] = len(month_stats['orders'])

    return stats


def get_sample_customer() -> List[tuple]:
    """获取一个样例客户编号"""
    with DB_ZXY() as connection:
        with connection.cursor() as cursor:
            query = f"""
            SELECT DISTINCT {FIELD_MAPPING['order']['customer_code']}, customername 
            FROM {TABLE_ORDER} 
            WHERE {FIELD_MAPPING['order']['customer_code']} IS NOT NULL 
            LIMIT 5
            """
            cursor.execute(query)
            customers = cursor.fetchall()
            return customers


def print_usage():
    """打印使用说明"""
    print("使用方法:")
    print("1. 不带参数运行，将显示样例客户并使用第一个客户进行查询")
    print("2. 带客户编号参数运行，将查询指定客户的订单统计")
    print("示例: python order_stats.py C202507020370")
    print("\n字段映射说明:")
    print("订单表字段:")
    for business_name, db_field in FIELD_MAPPING['order'].items():
        print(f"  - {business_name}: {db_field}")
    print("\n订单商品表字段:")
    for business_name, db_field in FIELD_MAPPING['order_goods'].items():
        print(f"  - {business_name}: {db_field}")


if __name__ == "__main__":
    import sys

    # 获取表字段信息
    print("获取表字段信息...")
    fields = get_table_fields()
    print(f"\n{TABLE_ORDER}表字段:")
    print(", ".join(fields[TABLE_ORDER]))
    print(f"\n{TABLE_ORDER_GOODS}表字段:")
    print(", ".join(fields[TABLE_ORDER_GOODS]))

    # 检查是否提供了客户编号参数
    if len(sys.argv) > 1:
        test_customer = sys.argv[1]
        print(f"\n使用提供的客户编号 {test_customer} 进行查询...")
    else:
        # 获取样例客户
        print("\n获取样例客户信息...")
        sample_customers = get_sample_customer()
        if not sample_customers:
            print("未找到任何客户信息")
            print_usage()
            sys.exit(1)

        print("找到以下客户:")
        for customer in sample_customers:
            print(f"客户编号: {customer[0]}, 客户名称: {customer[1]}")

        # 使用第一个客户进行测试
        test_customer = sample_customers[0][0]
        print(f"\n使用客户 {test_customer} 进行测试查询...")

    # 执行查询
    orders = get_customer_orders(test_customer)

    if orders:
        stats = calculate_performance_stats(orders)

        print("\n" + "=" * 50)
        print("统计结果汇总")
        print("=" * 50)
        print(f"订单总数: {stats['total_orders']} 单")
        print(f"销售总额: ￥{stats['total_amount']:.2f}")
        print(f"平均单价: ￥{(stats['total_amount'] / stats['total_orders']):.2f}" if stats[
                                                                                         'total_orders'] > 0 else "平均单价: ￥0.00")

        print("\n" + "-" * 50)
        print("商品销售明细")
        print("-" * 50)
        for goodsname, data in sorted(stats['goods_stats'].items(), key=lambda x: x[1]['amount'], reverse=True):
            print(f"商品名称: {goodsname}")
            print(f"销售数量: {data['quantity']}")
            print(f"销售金额: ￥{data['amount']:.2f}")
            print(
                f"单品均价: ￥{(data['amount'] / data['quantity']):.2f}" if data['quantity'] > 0 else "单品均价: ￥0.00")
            print("-" * 50)

        print("\n" + "-" * 50)
        print("月度销售趋势")
        print("-" * 50)
        for month, data in sorted(stats['monthly_stats'].items(), reverse=True):
            print(f"时间: {month}")
            print(f"订单数: {data['orders']} 单")
            print(f"销售额: ￥{data['amount']:.2f}")
            print(f"平均单价: ￥{(data['amount'] / data['orders']):.2f}" if data['orders'] > 0 else "平均单价: ￥0.00")
            print("-" * 50)
    else:
        print(f"未找到客户 {test_customer} 的订单信息")
